/*
 * Classe que representa a fachada da camada de persistência e conecta o BD
 */
package persistencia;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;


public class FacadePersistence {

    private Connection conn;

    public FacadePersistence() {
    }

    public void connect(String usuario, String senha, String host, int porta, String sid) throws SQLException, ClassNotFoundException {
        this.conn = new ConnectionDAO(usuario, senha, host, porta, sid).getConnection();
    }
    
    /*
     * Método que obtém a lista de owners
     */
    public List<String> getOwners() throws SQLException {
        List<String> l = new ArrayList<String>();
        String sql, r;

        sql = "SELECT owner FROM all_tables group by owner";

        PreparedStatement stmt = conn.prepareStatement(sql);
        ResultSet rst = stmt.executeQuery();

        while (rst.next()) {
            r = rst.getString("OWNER");
            l.add(r);
        }
        stmt.close();
        return l;
    }
    
    /*
     * Método que cria a estrutura no BD para o cálculo volumétrico
     * SCRIPTS DISPONÍVEIS NO MATERIAL (script.sql)
     */
    public boolean createStructure() throws SQLException
    {
        try
        {
            //--cria tipo t_column
            String query_t_collum = "CREATE OR REPLACE TYPE t_column AS OBJECT ( column_name VARCHAR2(30), data_type VARCHAR2(30), data_length INTEGER, data_precision INTEGER, nullable VARCHAR2(1), par_avg_size NUMBER(8,2), par_percent_null NUMBER(8,2))";
            PreparedStatement t_collum = conn.prepareStatement(query_t_collum);
            t_collum.execute();

            //--cria tipo t_columns como tabela do tipo t_column
            String query_t_collums = "CREATE OR REPLACE TYPE t_columns AS TABLE OF t_column";
            PreparedStatement t_collums = conn.prepareStatement(query_t_collums);
            t_collums.execute();


            //--cria tipo t_table que contem t_columns (uma lista de colunas)
            String query_t_table = "CREATE OR REPLACE TYPE t_table AS OBJECT (table_name VARCHAR2(30), column_list t_columns, table_space VARCHAR2(100),  pct_free INTEGER, par_records INTEGER, par_growth_rate NUMBER(8,2), par_retention_time INTEGER, liquido INTEGER, bruto INTEGER, liquido_ini INTEGER, liquido_next INTEGER, bruto_ini INTEGER, bruto_next INTEGER );";
            PreparedStatement t_table = conn.prepareStatement(query_t_table);
            t_table.execute();

            //-- cria tipo t_tables como tabela do tipo t_table
            String query_t_tables = "CREATE OR REPLACE TYPE t_tables AS TABLE OF t_table";
            PreparedStatement t_tables = conn.prepareStatement(query_t_tables);
            t_tables.execute();


            //-- cria tipo t_owner
            String query_t_owner = "CREATE OR REPLACE TYPE t_owner AS OBJECT (  owner VARCHAR(30),table_list t_tables)";
            PreparedStatement t_owner = conn.prepareStatement(query_t_owner);
            t_owner.execute();

            //-- única tabela do sistema, que armazena tudo sobre um owner
            String query_owner = "CREATE TABLE owners OF t_owner (owner not null) NESTED TABLE table_list STORE AS ntab_owner_tables ( NESTED TABLE column_list STORE AS ntab_table_columns )";
            PreparedStatement owner = conn.prepareStatement(query_owner);
            owner.execute();
            
            //--cria procedure para carga dos dados.
            String query_procedure_insere_owner = "CREATE OR REPLACE PROCEDURE P_INSERE_OWNER (p_owner VARCHAR2) IS  BEGIN INSERT INTO OWNERS VALUES (T_OWNER(p_owner,T_TABLES())); FOR c_table IN (SELECT * FROM all_tables where owner=p_owner)  LOOP INSERT INTO  TABLE(SELECT O.TABLE_LIST FROM OWNERS O WHERE O.OWNER=p_owner) T VALUES (T_TABLE(c_table.table_name,T_COLUMNS(),c_table.tablespace_name,c_table.pct_free,nvl(c_table.num_rows,0),0,5,0,0,0,0,0,0)); FOR c_column IN (SELECT * FROM all_tab_columns a where a.owner=p_owner and a.table_name = c_table.table_name) LOOP INSERT INTO  TABLE(SELECT T.COLUMN_LIST FROM  TABLE(SELECT O.TABLE_LIST FROM OWNERS O WHERE O.OWNER=p_owner) T WHERE T.TABLE_NAME = c_column.table_name) VALUES (T_COLUMN(c_column.column_name, c_column.data_type, c_column.data_length, c_column.data_precision, c_column.nullable, c_column.data_length, 0)); END LOOP; END LOOP; COMMIT; END;";
            PreparedStatement procedure_insere_owner = conn.prepareStatement(query_procedure_insere_owner);
            procedure_insere_owner.execute();
            
            //--cria procedure para carga dos dados.
            String query_P_VOLUMETRIC_CALCULATION = "CREATE OR REPLACE PROCEDURE P_VOLUMETRIC_CALCULATION (p_owner VARCHAR2) IS BEGIN declare v_used           NUMBER; v_alloc          NUMBER; v_rec            NUMBER; v_tcc          SYS.CREATE_TABLE_COST_COLUMNS; v_count         NUMBER;  begin FOR c_table IN (SELECT * FROM TABLE(SELECT o.table_list FROM OWNERS O WHERE o.owner=p_owner)) LOOP SELECT COUNT(*) into v_count FROM TABLE(SELECT T.COLUMN_LIST FROM TABLE(SELECT O.TABLE_LIST FROM OWNERS O WHERE O.OWNER=p_owner) T WHERE T.TABLE_NAME = c_table.table_name); IF v_count = 0 THEN v_tcc := null; ELSE v_tcc := SYS.CREATE_TABLE_COST_COLUMNS (); FOR c_column IN(SELECT * FROM TABLE(SELECT T.COLUMN_LIST FROM TABLE(SELECT O.TABLE_LIST FROM OWNERS O WHERE O.OWNER=p_owner) T WHERE T.TABLE_NAME = c_table.table_name)) LOOP  v_tcc.EXTEND; v_tcc(v_tcc.LAST):=SYS.create_table_cost_colinfo(c_column.data_type, c_column.par_avg_size * (1 - c_column.par_percent_null)); END LOOP; END IF; v_rec := c_table.par_records; IF v_rec = 0 THEN v_rec:=1; END IF; IF  v_tcc is null OR c_table.table_space is null THEN v_used := 0; v_alloc := 0; ELSE DBMS_SPACE.CREATE_TABLE_COST (c_table.table_space, v_tcc,  v_rec, c_table.pct_free, v_used, v_alloc); END IF; UPDATE TABLE(SELECT o.table_list FROM owners o WHERE o.owner=p_owner) t SET t.liquido = v_used, t.bruto = v_alloc, t.liquido_ini = v_used * power((1+(c_table.par_growth_rate/100)),2), t.liquido_next = v_used * power((1+(c_table.par_growth_rate/100)),(c_table.par_retention_time-2)), t.bruto_ini = v_alloc * power((1+(c_table.par_growth_rate/100)),2), t.bruto_next = v_alloc * power((1+(c_table.par_growth_rate/100)),(c_table.par_retention_time-2)) WHERE t.table_name=c_table.table_name; COMMIT; v_tcc := NULL; END LOOP; end; END;";
            PreparedStatement P_VOLUMETRIC_CALCULATION = conn.prepareStatement(query_P_VOLUMETRIC_CALCULATION);
            P_VOLUMETRIC_CALCULATION.execute();
                        
          
            
            return true;
        }
        catch (SQLException ex)
        {
            dropStructure();
            throw ex;
        }
    }    
    
    public boolean reCreateStructure(String owner) throws SQLException
    {
        if(hasStructure(owner))
        {
            dropStructure();
        }
        createStructure();
        return true;
    }
    
    public boolean hasStructure(String owner) throws SQLException
    {
        StringBuilder st = new StringBuilder();
        st.append("SELECT * FROM ALL_ALL_TABLES where owner = '"+owner+"' AND table_name = 'OWNERS'");
        
        PreparedStatement stmt = conn.prepareStatement(st.toString());        
        int result  = stmt.executeUpdate();
        return (result > 0);
    }
    
    private void dropStructure()
    {
        PreparedStatement stmt;
        try 
        {
            stmt = conn.prepareStatement("drop table owners");
            stmt.execute();
        } 
        catch (SQLException ex) { }
        
        try 
        {
            stmt = conn.prepareStatement("drop type t_owner");
            stmt.execute();
        } 
        catch (SQLException ex) { }
        
        try 
        {
            stmt = conn.prepareStatement("drop type t_tables");
            stmt.execute();
        } 
        catch (SQLException ex) { }
        
        try 
        {
            stmt = conn.prepareStatement("drop type t_table");
            stmt.execute();
        } 
        catch (SQLException ex) { }
        
         try 
        {
            stmt = conn.prepareStatement("drop type t_columns");
            stmt.execute();
        } 
        catch (SQLException ex) { }
         
        try 
        {
            stmt = conn.prepareStatement("drop type t_column");
            stmt.execute();
        } 
        catch (SQLException ex) { }
        
        
        try 
        {
            stmt = conn.prepareStatement("drop PROCEDURE P_INSERE_OWNER");
            stmt.execute();
        } 
        catch (SQLException ex) { }              
        
        try 
        {
            stmt = conn.prepareStatement("drop PROCEDURE P_VOLUMETRIC_CALCULATION");
            stmt.execute();
        } 
        catch (SQLException ex) { }              
    }
    
    /*
     * Método que chama a procedure que carrega os dados do owner na tabela objeto-relacional
     */
    public void setOwner(String owner) throws SQLException
    {    
        if(!hasOwner(owner))
        {
            String queryCall_P_INSERE_OWNER = "{CALL P_INSERE_OWNER(?)}";
            CallableStatement cs = conn.prepareCall(queryCall_P_INSERE_OWNER);
            cs.setString(1, owner);
            cs.execute();      
            cs.close();
        }
    }
    
    private boolean hasOwner(String owner) throws SQLException
    {
        String queryHasOwner = "select count(o.owner) as count from owners o where owner = '"+owner+"'";
        PreparedStatement stmt = conn.prepareStatement(queryHasOwner);
        ResultSet result = stmt.executeQuery();
        
        while (result.next()) 
        {
            int count = result.getInt("count");
            return (count > 0);
        }
        return false;
    }



    /*
     * Método que busca as tabelas de determinado owner carregado no banco para
     * a simulação.
     */
    public List<String> getTables(String owner) throws SQLException {
        List<String> l = new ArrayList<String>();
        String sql, r;

        sql = "select "
                + "t.table_name, "
                + "t.par_records, "
                + "t.par_growth_rate, "
                + "t.par_retention_time "
                + "from "
                + "table("
                + "select distinct "
                + "o.table_list "
                + "from "
                + "owners o "
                + "where o.owner=?) t";

        PreparedStatement stmt = conn.prepareStatement(sql);
        stmt.setString(1, owner);
        ResultSet rst = stmt.executeQuery();

        while (rst.next()) {
            r = rst.getString("TABLE_NAME") + ";";
            r = r + rst.getString("PAR_RECORDS") + ";";
            r = r + rst.getString("PAR_GROWTH_RATE") + ";";
            r = r + rst.getString("PAR_RETENTION_TIME") + ";";
            l.add(r);
        }
        stmt.close();
        return l;
    }
    

    /*
     * Método que obtém as colunas de determinada tabela e owner do banco, carregada na
     * tabela de simulação
     */
    public List<String> getColumns(String owner, String table) throws SQLException {
        List<String> l = new ArrayList<String>();
        String sql, r;

        sql = "select "
                + "c.column_name, "
                + "c.data_type, "
                + "c.data_length, "
                + "c.data_precision, "
                + "c.nullable, "
                + "c.par_avg_size, "
                + "c.par_percent_null "
                + "from "
                + "table("
                + "select "
                + "t.column_list "
                + "from "
                + "owners o, "
                + "table(o.table_list) t "
                + "where "
                + "o.owner=? "
                + "and "
                + "t.table_name=?) c";


        PreparedStatement stmt = conn.prepareStatement(sql);
        stmt.setString(1, owner);
        stmt.setString(2, table);
        ResultSet rst = stmt.executeQuery();

        while (rst.next()) {
            r = rst.getString("COLUMN_NAME") + ";";
            r = r + rst.getString("DATA_TYPE") + ";";
            r = r + rst.getString("DATA_LENGTH") + ";";
            r = r + rst.getString("DATA_PRECISION") + ";";
            r = r + rst.getString("NULLABLE") + ";";
            r = r + rst.getString("PAR_AVG_SIZE") + ";";
            r = r + rst.getString("PAR_PERCENT_NULL") + ";";
            l.add(r);
        }
        stmt.close();
        return l;
    }

    /*
     * Método que grava no banco os dados das tabelas alterados pelo usuário na aplicação
     */
    public boolean writeDataTables(String owner, String table, int records, double growth, int retention) throws SQLException {
        String sql;
        
        sql = "update "
                + "table("
                    + "select "
                        + "o.table_list "
                    + "from "
                        + "owners o "
                    + "where "
                        + "o.owner=?) t "
                + "set "
                    + "t.par_records=?, "
                    + "t.par_growth_rate=?, "
                    + "t.par_retention_time=? "
                + "where "
                    + "t.table_name=?";                                
                
        PreparedStatement stmt = conn.prepareStatement(sql);
        stmt.setString(1, owner);       
        stmt.setInt(2, records);
        stmt.setDouble(3, growth);
        stmt.setInt(4, retention);
        stmt.setString(5, table);
        boolean result = stmt.execute();
        stmt.close();
        return result;
    }

     /*
     * Método que grava no banco os dados das colunas alterados pelo usuário na aplicação
     */
    public boolean writeDataColumns(String owner, String table, String column, double avg, double percent) throws SQLException {
        String sql;
        sql = "update "
                + "table("
                    + "select "
                    + "t.column_list "
                    + "from "
                        + "owners o, "
                        + "table(o.table_list) t "
                    + "where "
                        + "o.owner=? "
                        + "and "
                        + "t.table_name=?) c "
                + "set "                
                + "c.par_avg_size=?, "
                + "c.par_percent_null=? "
                + "where c.column_name=?";                
        
        PreparedStatement stmt = conn.prepareStatement(sql);
        stmt.setString(1, owner);
        stmt.setString(2, table);
        stmt.setDouble(3, avg);
        stmt.setDouble(4, percent);
        stmt.setString(5, column);
        
        boolean result = stmt.execute();
        stmt.close();
        return result;
    }
    
    /*
     * Método que chama a procedure que realiza o cálculo volumétrico no banco.
     * Obtém o resultado da simulação realizada.
     */
    
    public List<String> restultCalc(String owner) throws SQLException
    {
        //realizar calculo
        String queryP_VOLUMETRIC_CALCULATION = "{CALL P_VOLUMETRIC_CALCULATION(?)}";
        CallableStatement cs = conn.prepareCall(queryP_VOLUMETRIC_CALCULATION);
        cs.setString(1, owner);
        cs.execute();      
        cs.close();

        List<String> resultCalc = new ArrayList<String>();        
        String queryResultCalc = "select t.table_name, t.liquido, t.bruto, t.liquido_ini, t.liquido_next, t.bruto_ini, t.bruto_next from owners o, table(o.table_list) t where o.owner = '"+owner+"'";
        PreparedStatement stmt = conn.prepareStatement(queryResultCalc);
        ResultSet rst = stmt.executeQuery();
        while (rst.next()) 
        {
            StringBuilder st = new StringBuilder(7);
            st.append(rst.getString("table_name"));
            st.append(";");
            st.append(rst.getDouble("bruto"));
            st.append(";");
            st.append(rst.getDouble("liquido"));
            st.append(";");
            st.append(rst.getDouble("bruto_ini"));
            st.append(";");
            st.append(rst.getDouble("liquido_ini"));
            st.append(";");
            st.append(rst.getDouble("bruto_next"));
            st.append(";");
            st.append(rst.getDouble("liquido_next"));
            st.append(";");
            resultCalc.add(st.toString());
        }
        
        stmt.close();
        return resultCalc;
    }
    
 
    
}
